﻿using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text.RegularExpressions;
using System.Web;
using Microsoft.SqlServer;

namespace DataApi
{
    /// <summary>
    /// Manages sql connection and data handling
    /// </summary>
    public static class EpicDataHelper
    {
        /// <summary>
        /// Runs the method for getting data header information for the table
        /// </summary>
        /// <param name="table_name">Anonymous parameter: The table string to get header inforation from</param>
        /// <returns>A collection of data of the table information</returns>
        public static List<Dictionary<String, String>> GetDataHeaderAnon(Object table_name)
        {
            return GetDataHeader((String)table_name);
        }

        /// <summary>
        /// Runs the method for getting data header information for the table
        /// </summary>
        /// <param name="table_name">The table to get header inforation from</param>
        /// <returns>A collection of data of the table information</returns>
        public static List<Dictionary<String, String>> GetDataHeader(String table_name)
        {
            var params_list = new NameValueCollection();
            params_list.Add("contentname", table_name);

            return ConvertDataSetToList(GetStoredProcedureResults(EpicCmsConstants.GetDataHeaderProc, params_list));
        }

        /// <summary>
        /// Runs the method for getting data by an id
        /// </summary>
        /// <param name="table_name">Anonymous parameter(String): The id of the data to get</param>
        /// <returns>A collection of data</returns>
        public static List<Dictionary<String, String>> GetDataByIdAnon(Object id)
        {
            return GetDataById((String)id);
        }

        /// <summary>
        /// Runs the method for getting data by an id
        /// </summary>
        /// <param name="table_name">The id of the data to get</param>
        /// <returns>A collection of data</returns>
        public static List<Dictionary<String, String>> GetDataById(String id)
        {
            var params_list = new NameValueCollection();
            params_list.Add("id", id);

            return ConvertDataSetToList(GetStoredProcedureResults(EpicCmsConstants.GetDataByIdProc, params_list));
        }

        /// <summary>
        /// Runs the method for getting data from a table
        /// </summary>
        /// <param name="table_name">Anonymous parameter(String): The table to get the data from</param>
        /// <param name="id">Anonymous parameter(String), Optional: The id of data to get from the table</param>
        /// <returns>A collection of data from the table</returns>     
        public static List<Dictionary<String, String>> GetDataFromTableAnon(Object table_name, Object id)
        {
            return GetDataFromTable((String)table_name, (String)id);
        }

        /// <summary>
        /// Runs the method for getting data from a table
        /// </summary>
        /// <param name="table_name">The table to get the data from</param>
        /// <param name="id">Optional: The id of data to get from the table</param>
        /// <returns>A collection of data from the table</returns>        
        public static List<Dictionary<String, String>> GetDataFromTable(String table_name, String id)
        {
            var params_list = new NameValueCollection();
            params_list.Add("tablename", table_name);
            params_list.Add("id", id);

            return ConvertDataSetToList(GetStoredProcedureResults(EpicCmsConstants.GetTableDataProc, params_list));
        }

        /// <summary>
        /// Runs a method given the method name and returns the results
        /// </summary>
        /// <param name="proc_name">Anonymous parameter(String): The name of the method to run</param>
        /// <param name="params_list">Anonymous parameter(NameValueCollection): The collection of parameters to pass with the method</param>
        /// <returns>A collection of data from the table</returns>   
        public static List<Dictionary<String, String>> ExecuteStoredProcedureAnon(Object proc_name, Object params_list)
        {
            return ExecuteStoredProcedure((String)proc_name, (NameValueCollection)params_list);
        }

        /// <summary>
        /// Runs a method given the method name and returns the results
        /// </summary>
        /// <param name="proc_name">The name of the method to run</param>
        /// <param name="params_list">The collection of parameters to pass with the method</param>
        /// <returns>A collection of data from the table</returns>    
        public static List<Dictionary<String, String>> ExecuteStoredProcedure(String proc_name, NameValueCollection params_list)
        {
            return ConvertDataSetToList(GetStoredProcedureResults(proc_name, params_list));
        }

        public static List<Dictionary<String, String>> RunDatabaseSetupProcedures()
        {
            return RunDatabaseSetupProcedures(null);
        }

        public static List<Dictionary<String, String>> RunDatabaseSetupProcedures(Object db_name)
        {
            return RunDatabaseSetupProcedures((String)db_name);
        }

        public static List<Dictionary<String, String>> RunDatabaseSetupProcedures(String db_name)
        {
            string scriptDirectory = HttpContext.Current.Server.MapPath("~/Database");
            DirectoryInfo di = new DirectoryInfo(scriptDirectory);
            FileInfo[] rgFiles = di.GetFiles("*.sql");

            string connectionstring =
                ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString();
            using (SqlConnection connection = new SqlConnection(connectionstring))
            {
                connection.Open();
                foreach (FileInfo fi in rgFiles)
                {
                    if (fi.Name != "dataapi.Database.sql")
                    {
                        FileInfo fileInfo = new FileInfo(fi.FullName);
                        string script = fileInfo.OpenText().ReadToEnd();

                        IEnumerable<string> commandStrings = Regex.Split(script, "^\\s*GO\\s*$", RegexOptions.Multiline);
                        foreach (string commandString in commandStrings)
                        {
                            if (commandString.Trim() != "")
                            {
                                string commandLine = commandString;
                                if(!String.IsNullOrEmpty(db_name))
                                    commandLine = commandString.Replace("[dataapi]", "[" + db_name + "]");

                                new SqlCommand(commandLine, connection).ExecuteNonQuery();
                            }
                        }
                    }
                }
            }

            return new List<Dictionary<string, string>>();
        }

        private static DataSet ExecuteSqlCommand(SqlCommand command)
        {
            string connectionstring =
                ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString();
            SqlConnection connection = new SqlConnection(connectionstring);
            connection.Open();
            command.Connection = connection;
            SqlDataAdapter oAdptr = new SqlDataAdapter(command);
            DataSet oDS = new DataSet();
            //command.ExecuteNonQuery();
            command.CommandTimeout = 600;
            oAdptr.Fill(oDS);
            connection.Close();

            return oDS;
        }

        private static DataSet ExecuteSqlWithParams(String query, NameValueCollection params_list, bool is_storedproc)
        {
            SqlCommand oCmd = new SqlCommand(query);

            foreach (string parameter_name in params_list.AllKeys)
            {
                SqlParameter param = new SqlParameter(parameter_name, params_list[parameter_name]);
                oCmd.Parameters.Add(param);
            }

            if (is_storedproc)
                oCmd.CommandType = CommandType.StoredProcedure;

            return ExecuteSqlCommand(oCmd);
        }

        private static DataSet GetStoredProcedureResults(String proc_name, NameValueCollection params_list)
        {
            return ExecuteSqlWithParams(proc_name, params_list, true);
        }

        private static List<Dictionary<String, String>> ConvertDataSetToList(DataSet source_ds)
        {
            var ret = new List<Dictionary<string, string>>();
            var col_count = source_ds.Tables[0].Columns.Count;

            foreach (DataRow row in source_ds.Tables[0].Rows)
            {
                var newlist = new Dictionary<string, string>();

                for (int i = 0; i < col_count; i++)
                {
                    newlist.Add(source_ds.Tables[0].Columns[i].ColumnName, row[i].ToString());
                }

                ret.Add(newlist);
            }

            return ret;
        }
    }
}